package ro.isdc.navigator.retriever.jdbc;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import ro.isdc.navigator.retriever.IRetriever;
import ro.isdc.navigator.retriever.jdbc.strategy.IComputePaginatedSQLStrategy;
import ro.isdc.navigator.retriever.jdbc.strategy.PaginatedSQL;
import ro.isdc.navigator.retriever.jdbc.strategy.SQLType;

/**
 * The JDBC retriever, responsible for creating, for populating and running SQLs.
 * 
 * @author Andrei Chirila / ISDC! Romania
 * @version 1.0
 * @date Jun 30, 2009
 * @param <T>
 *          the type of object that the results are expected to be
 */
public class JDBCRetriever<T> implements IRetriever<T> {

  /**
   * Logger
   */
  private static final Log LOGGER = LogFactory.getLog(JDBCRetriever.class);

  private static final String STANDARD_SQL_STRING_WITHOUT_SELECT = "from .*";

  private static final Pattern pattern = Pattern.compile(STANDARD_SQL_STRING_WITHOUT_SELECT);

  /**
   * The SQL
   */
  private String SQL;

  /**
   * The list of argument names;
   */
  private List<String> argsNames;

  /**
   * Template class with a basic set of JDBC operations, allowing the use of
   * named parameters rather than traditional '?' placeholders. This class
   * delegates to a wrapped JdbcTemplate once the substitution from named
   * parameters to JDBC style '?' placeholders is done at execution time. It
   * also allows for expanding a List of values to the appropriate number of
   * placeholders.
   */
  private NamedParameterJdbcTemplate jDBCTemplate;

  /**
   * Row Mapper for a specific type class.
   */
  private ParameterizedRowMapper<T> rowMapper;

  /**
   * The pagination strategy
   */
  private IComputePaginatedSQLStrategy strategy;

  /**
   * @param dataSource
   *          the dataSource to set
   */
  public void setDataSource(DataSource dataSource) {
    jDBCTemplate = new NamedParameterJdbcTemplate(dataSource);
  }

  /**
   * @param SQL
   *          the SQL to set
   */
  public void setSQL(final String sql) {
    this.SQL = sql;
  }

  /**
   * @param argsNames
   *          the argsNames to set
   */
  public void setArgsNames(final List<String> argNames) {
    this.argsNames = argNames;
  }

  /**
   * @param rowMapperClass
   *          the rowMapperClass to set
   * @throws IllegalAccessException
   *           if the access is illegal
   * @throws InstantiationException
   *           if the object cannot be instantiated
   */
  public void setRowMapperClass(
      final Class<ParameterizedRowMapper<T>> rowMapperClass)
      throws InstantiationException, IllegalAccessException {
    this.rowMapper = rowMapperClass.newInstance();
  }

  /**
   * 
   * @param strategyClass
   *          the strategyClass to set
   * @throws InstantiationException
   *           if the access is illegal
   * @throws IllegalAccessException
   *           if the object cannot be instantiated
   */
  public void setStrategyClass(
      final Class<IComputePaginatedSQLStrategy> strategyClass)
      throws InstantiationException, IllegalAccessException {
    this.strategy = strategyClass.newInstance();
  }

  /**
   * {@inheritDoc}
   */
  public long getNumberOfResults(List<Object> args) {
    
    if (argsNames == null || argsNames.isEmpty()) {
      if (args == null) {
        args = new ArrayList<Object>();
      }

      return jDBCTemplate.getJdbcOperations().queryForInt(buildCountSQL(),
          args.toArray());
    }

    if (args == null || args.isEmpty()) {
      throw new IllegalStateException(
          "You haven't specified any arguments in \"countSQL\" although you have aguments names!");
    }

    if (argsNames.size() != args.size()) {
      throw new IllegalStateException(
          "The number of param values is not equal to the number of the param names in \"countSQL\"");
    }

    Object[] paramValues = args.toArray();
    String[] paramNames = argsNames.toArray(new String[argsNames.size()]);

    Map<String, Object> namedParameters = new HashMap<String, Object>();
    for (int i = 0; i < paramValues.length; i++) {
      namedParameters.put(paramNames[i], paramValues[i]);
    }

    return jDBCTemplate.queryForInt(buildCountSQL(), namedParameters);

  }

  /**
   * {@inheritDoc}
   */
  public List<T> getResults(final int startIndex, final int blockSize,
      List<Object> args) {

    if (strategy == null) {
      throw new IllegalStateException("You haven't specified the DB strategy!");
    }
    
    if (argsNames == null || argsNames.isEmpty()) {
      return getResultsForIndexedParam(startIndex, blockSize, args);
    }

    if (args == null || args.isEmpty()) {
      throw new IllegalStateException(
          "You haven't specified any arguments in \"SQL\" although you have aguments names!");
    }

    if (argsNames.size() != args.size()) {
      throw new IllegalStateException(
          "The number of param values is not equal to the number of the param names in \"SQL\"");
    }

    return getResultsForNamedParam(startIndex, blockSize, args);
  }

  /**
   * Gets the results when having an SQL with indexed parameters
   * 
   * @param startIndex
   *          the start index
   * @param blockSize
   *          the block size
   * @param args
   *          the arguments
   * @return the list of results
   */
  @SuppressWarnings("unchecked")
  private List<T> getResultsForIndexedParam(final int startIndex,
      final int blockSize, final List<Object> args) {
    List<Object> finalArgs = new ArrayList<Object>();
    if (args != null) {
      finalArgs.addAll(args);
    }

    PaginatedSQL generatedQuery = strategy.computePaginatedSQL(SQL.toLowerCase(),
        SQLType.INDEXED, startIndex, blockSize);
    LOGGER.info("JDBC: " + generatedQuery.getPaginatedSQL());

    finalArgs.add(generatedQuery.getLeftLimit());
    finalArgs.add(generatedQuery.getRightLimit());
    return jDBCTemplate.getJdbcOperations().query(
        generatedQuery.getPaginatedSQL(), finalArgs.toArray(), rowMapper);
  }

  /**
   * Gets the results when having an SQL with named parameters.
   * 
   * @param startIndex
   *          the start index
   * @param blockSize
   *          the block size
   * @param args
   *          the arguments
   * @return the list of results
   */
  @SuppressWarnings("unchecked")
  private List<T> getResultsForNamedParam(final int startIndex,
      final int blockSize, final List<Object> args) {
    Object[] paramValues = args.toArray();
    String[] paramNames = argsNames.toArray(new String[argsNames.size()]);

    Map<String, Object> namedParameters = new HashMap<String, Object>();
    for (int i = 0; i < paramValues.length; i++) {
      namedParameters.put(paramNames[i], paramValues[i]);
    }

    PaginatedSQL generatedQuery = strategy.computePaginatedSQL(SQL.toLowerCase(),
        SQLType.NAMED, startIndex, blockSize);
    LOGGER.info("JDBC: " + generatedQuery.getPaginatedSQL());

    namedParameters.put("start", generatedQuery.getLeftLimit());
    namedParameters.put("end", generatedQuery.getRightLimit());

    return jDBCTemplate.query(generatedQuery.getPaginatedSQL(),
        namedParameters, rowMapper);
  }

  /**
   * Gets the count SQL. If it is specified, this value is taken into
   * consideration. Otherwise, its being deduced from the original SQL.
   * 
   * @return the count SQL
   */
  private String buildCountSQL() {
    Matcher matcher = pattern.matcher(SQL.toLowerCase());
    matcher.find();
    return "select count(*) " + matcher.group();
  }

}
